package com.eipc.util;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.androidpn.sdk.Constants;
import org.androidpn.sdk.XmppManager;
import org.json.JSONArray;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.content.SharedPreferences;
import android.content.SharedPreferences.Editor;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.AsyncTask;
import android.util.Log;

import com.eipc.CommonActivity;
import com.eipc.CommonTabActivity;
import com.eipc.model.Department;
import com.eipc.model.ImportNotice;
import com.eipc.model.News;
import com.eipc.model.Person;
import com.eipc.model.Position;
import com.eipc.model.Reply;
import com.eipc.model.Staff;

/**
 * 
 * @author x_liaolijun
 * 
 * 2010-9-7 下午02:23:28
 * 
 * MidDbHelper
 * 
 */
public class DBHelper extends android.database.sqlite.SQLiteOpenHelper {

	public final static int APP_TYPE_IMPORT_NOTICE = 1; // 及时信息
	public final static int APP_TYPE_ME_REPORT = 2; // 我来找茬
//	public final static int APP_TYPE_WORK_REPORT = 3; // 工作汇报

	private SharedPreferences sdkPreferences;

	PreferencesHelper mHelper;
	SQLiteDatabase db;
	Context context;
	private static DBHelper instance;

	public static synchronized DBHelper getInstance(Context context) {
		if (instance == null) {
			instance = new DBHelper(context);
		}
		return instance;
	}

	private DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
		mHelper = new PreferencesHelper(context.getSharedPreferences(
				Constants.SDK_PREFERENCES, Context.MODE_PRIVATE));
//		try {
//			SharedPreferences sdkPreferences = context.getSharedPreferences(
//					Constants.SDK_PREFERENCES, Context.MODE_PRIVATE);
//			String uploadReplyStatus = sdkPreferences.getString(
//					"UPLOAD_REPLY_STATUS", "1");
//			Log.e("-------uploadReplyStatus-----------", uploadReplyStatus);
//			if ("1".equals(uploadReplyStatus)) {
//				openDB();
//				db.execSQL(TB_DELETE + TB_UPLOAD_REPLY);
//				db.execSQL(SQL_UPLOAD_REPLY);
//				initUploadReply();
//				Editor editor = sdkPreferences.edit();
//				editor.putString("UPLOAD_REPLY_STATUS", "2");
//				editor.commit();
//			}
//		} catch (Exception e) {
//			Log.e("----uploadReplysql-----", "-----error-----", e);
//		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL(SQL_POSITION);
			db.execSQL(SQL_DEPARTMENT);
			db.execSQL(SQL_IMPORT_NOTICE);
			db.execSQL(SQL_IMPORT_NOTICE_SEND);
			db.execSQL(SQL_IMPORT_NOTICE_SEND_CHILD);
			db.execSQL(SQL_PERSON);


			db.execSQL(SQL_TB_VERSION);

//			// for test
//			insertFromAsset(db, new String[]{"person.txt",
//					"postion.txt", "department.txt"});
			// insertFromAsset(db, new String[] { "person.txt", "person2.txt",
			// , "ground1.txt",
			// "person2.txt", });
			//
			// if (!mHelper.getBoolean("is_insert")) {
			// new ContactsTask().execute();
			// mHelper.put("is_insert", true);
			// }
		} catch (Exception e) {
			Log.e("DBHelper onCreate", "error");
		}

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			db.execSQL(TB_DELETE + TB_POSITION);
			db.execSQL(TB_DELETE + TB_DEPARTMENT);
			db.execSQL(TB_DELETE + TB_IMPORT_NOTICE);
			db.execSQL(TB_DELETE + TB_IMPORT_NOTICE_SEND);
			db.execSQL(TB_DELETE + TB_IMPORT_NOTICE_SEND_CHILD);
			db.execSQL(TB_DELETE + TB_PERSON);
//			db.execSQL(TB_DELETE + TB_PERSON2);
//			db.execSQL(TB_DELETE + TB_PLAYER);
//			db.execSQL(TB_DELETE + TB_MATCH);
//			db.execSQL(TB_DELETE + TB_PLAYER_GROUP);
//			db.execSQL(TB_DELETE + TB_DATASTATICSTICS);
//			db.execSQL(TB_DELETE + TB_GAMES);
//			db.execSQL(TB_DELETE + TB_TOTAL);
//			db.execSQL(TB_DELETE + TB_TURN);
//			db.execSQL(TB_DELETE + TB_SENTENCE_SCORE);
//			db.execSQL(TB_DELETE + TB_SCORE);
//			db.execSQL(TB_DELETE + TB_VENUES);
//			db.execSQL(TB_DELETE + TB_WEATHER);
//			db.execSQL(TB_DELETE + TB_CONSULT);

			db.execSQL(TB_DELETE + TB_VERSION);

			onCreate(db);
		} catch (Exception e) {
			Log.e("---------onUpgrade", "Error");
		}
	}

	/*
	 * 重文件中读取数据
	 */
	private void insertFromAsset(SQLiteDatabase db, String[] files) {
		AssetManager manager = context.getAssets();
		DataInputStream dis = null;
		BufferedReader bf = null;
		String temp = "";
		for (int i = 0; i < files.length; i++) {
			try {
				dis = new DataInputStream(manager.open(files[i]));
				bf = new BufferedReader(new InputStreamReader(dis));
				while ((temp = bf.readLine()) != null) {
					if (temp != null && !"".equals(temp))
						db.execSQL(temp);
				}
			} catch (Exception e) {
				Log.w("", e);
			} finally {
				try {
					if (bf != null)
						bf.close();
					if (dis != null)
						dis.close();
				} catch (IOException e) {
				}
			}
		}
	}

	void openDB() {
		try {
//			if (db == null)
				db = getWritableDatabase();
		} catch (SQLiteException ex) {
			db = getReadableDatabase();
		}
	}

	public String getMaxId(String table, String field) {
		openDB();
		String result = null;
		Cursor c = db.query(table, new String[] { "max(" + field + ")" }, null,
				null, null, null, null);
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			String r = c.getString(0);
			c.close();
			return r;
		}
		return result;
	}


	public void updateMaxId(String maxID) {
		SharedPreferences sdkPreferences = context.getSharedPreferences(
				Constants.SDK_PREFERENCES, Context.MODE_PRIVATE);
		Editor editor = sdkPreferences.edit();
		editor.putString(Constants.MaxId, String.valueOf(maxID));
		editor.commit();
	}

	
	public String getIDs() {
		String strResult = "";
		openDB();
		sdkPreferences = context.getSharedPreferences(
				Constants.SDK_PREFERENCES, Context.MODE_PRIVATE);
		int iMaxId = Integer.valueOf(sdkPreferences.getString(Constants.MaxId,
				"0"));
		String select = FIELD_IMPORT_NOTICE_ID + " > " + String.valueOf(iMaxId);
		Cursor c = db.query(TB_IMPORT_NOTICE,
				new String[] { FIELD_IMPORT_NOTICE_ID }, select, null, null,
				null, null);
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			do {
				String r = c.getString(c
						.getColumnIndexOrThrow(FIELD_IMPORT_NOTICE_ID));
				if (c.isLast()) {
					strResult += r;
				} else {
					strResult += r + ",";
				}
			} while (c.moveToNext());
		}
		if (c != null)
			c.close();
		return strResult;

	}
	
	public String getIMIDs() {
		String strResult = "";
		openDB();
//		sdkPreferences = context.getSharedPreferences(
//				Constants.SDK_PREFERENCES, Context.MODE_PRIVATE);
//		int iMaxId = Integer.valueOf(sdkPreferences.getString(Constants.MaxId,
//				"0"));
		String select = FIELD_IMPORT_NOTICE_ID + " > " + String.valueOf(0);
		Cursor c = db.query(TB_IMPORT_NOTICE,
				new String[] { FIELD_IMPORT_NOTICE_ID }, select, null, null,
				null, null);
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			do {
				String r = c.getString(c
						.getColumnIndexOrThrow(FIELD_IMPORT_NOTICE_ID));
				if (c.isLast()) {
					strResult += r;
				} else {
					strResult += r + ",";
				}
			} while (c.moveToNext());
		}
		if (c != null)
			c.close();
		if (c != null)
			return strResult;
		else
			return "";

	}
	
	public String GetIMMaxID()
	{
		if (" ".equals(getMaxId(TB_IMPORT_NOTICE,FIELD_ID))||getMaxId(TB_IMPORT_NOTICE,FIELD_ID)==null)
				return null;
		else
			return getMaxId(TB_IMPORT_NOTICE,FIELD_ID);
	}

	
	public void insertImportNotice(ImportNotice[] infos, String maxID) {
		Log.e("-----insert-------", infos.toString());
		if (infos != null) {
			openDB();
			ContentValues values;
			for (ImportNotice info : infos) {
				Log.e("-----insert-noticeId-------", String
						.valueOf(info.noticeId));
				try {
					Cursor c = queryAll(TB_IMPORT_NOTICE,
							new String[] { FIELD_IMPORT_NOTICE_ID },
							new String[] { String.valueOf(info.noticeId) });
					if (c == null || c.getCount() < 1) {
						Log.e("-----insert-begin-------", info.content);
						values = new ContentValues();
						values.put(FIELD_IMPORT_NOTICE_ID, info.noticeId);
						values.put(FIELD_IMPORT_NOTICE_IMAGE_LOCALURL,
								info.imageLocalUrl);
						values.put(FIELD_IMPORT_NOTICE_IMAGE_WEBURL,
								info.imageWebUrl);
						values
								.put(FIELD_IMPORT_NOTICE_SEND_TYPE,
										info.sendType);
						values.put(FIELD_IMPORT_NOTICE_APPTYPE, info.appType);
						values.put(FIELD_IMPORT_NOTICE_CONTENT, info.content);
						values.put(FIELD_IMPORT_NOTICE_ISSUER, info.issuer);
						values.put(FIELD_IMPORT_NOTICE_ISSUE_TIME,
								info.issueTime);
						values
								.put(FIELD_IMPORT_NOTICE_SEND_TIME,
										info.sendTime);
						values.put(FIELD_IMPORT_NOTICE_SENDER, info.sender);
						values.put(FIELD_IMPORT_NOTICE_ISREAD, info.isRead);
						values.put(FIELD_IMPORT_NOTICE_TOP, info.top);
						values.put(FIELD_IMPORT_NOTICE_REPLYCONTENT,
								info.replyContent);
						values.put(FIELD_IMPORT_NOTICE_REPLYTIME,
								info.replyTime);
						db.insert(TB_IMPORT_NOTICE, FIELD_ID, values);
					} else {
						Log.e("-----insert-noticeId-exsist-------", String
								.valueOf(info.noticeId));
					}
				} catch (Exception e) {
					Log.e("insert error : ", e.getMessage());
				}
				String strMaxID = getMaxId(TB_IMPORT_NOTICE,FIELD_ID);
				XmppManager.uploadNotification(context, true);
				Intent i = new Intent(CommonTabActivity.ACTION_POPUP_MESSAGE);
				i.putExtra("id", strMaxID);
				context.sendBroadcast(i);
			}
		}
	}

	public void insertImportNotice(ArrayList<ImportNotice> infos) {
		Log.e("-----insert-------", infos.toString());
		if (infos != null) {
			openDB();
			ContentValues values;
			// /String maxId =
			// getMaxId(TB_IMPORT_NOTICE,FIELD_IMPORT_NOTICE_ID);
			// Log.e("-----insert-maxId-------",maxId);
			for (ImportNotice info : infos) {
				Log.e("-----insert-noticeId-------", String
						.valueOf(info.noticeId));
				try {
					// if(maxId==null ||
					// Integer.valueOf(maxId).intValue()<Integer.valueOf(info.noticeId).intValue()){
					Cursor c = queryAll(TB_IMPORT_NOTICE,
							new String[] { FIELD_IMPORT_NOTICE_ID },
							new String[] { String.valueOf(info.noticeId) });
					if (c == null || c.getCount() < 1) {
						Log.e("-----insert-begin-------", info.content);
						values = new ContentValues();
						values.put(FIELD_IMPORT_NOTICE_ID, info.noticeId);
						values.put(FIELD_IMPORT_NOTICE_IMAGE_LOCALURL,
								info.imageLocalUrl);
						values.put(FIELD_IMPORT_NOTICE_IMAGE_WEBURL,
								info.imageWebUrl);
						values
								.put(FIELD_IMPORT_NOTICE_SEND_TYPE,
										info.sendType);
						values.put(FIELD_IMPORT_NOTICE_APPTYPE, info.appType);
						values.put(FIELD_IMPORT_NOTICE_CONTENT, info.content);
						values.put(FIELD_IMPORT_NOTICE_ISSUER, info.issuer);
						values.put(FIELD_IMPORT_NOTICE_ISSUE_TIME,
								info.issueTime);
						values
								.put(FIELD_IMPORT_NOTICE_SEND_TIME,
										info.sendTime);
						values.put(FIELD_IMPORT_NOTICE_SENDER, info.sender);
						values.put(FIELD_IMPORT_NOTICE_ISREAD, info.isRead);
						values.put(FIELD_IMPORT_NOTICE_TOP, info.top);
						values.put(FIELD_IMPORT_NOTICE_REPLYCONTENT,
								info.replyContent);
						values.put(FIELD_IMPORT_NOTICE_REPLYTIME,
								info.replyTime);
						db.insert(TB_IMPORT_NOTICE, FIELD_ID, values);
					} else {
						Log.e("-----insert-noticeId-exsist-------", String
								.valueOf(info.noticeId));
					}
					// }
				} catch (Exception e) {
					Log.e("insert error : ", e.getMessage());
				}
			}
		}
	}

	/**
	 * 更新TB_IMPORT_NOTICE_SEND的serverId
	 */
	public void updateServerId(int id, String serverId) {
		openDB();
		ContentValues values = new ContentValues();
		values.put(FIELD_IMPORT_NOTICE_SENT_SERVERID, serverId);
		db.update(TB_IMPORT_NOTICE_SEND, values, FIELD_ID + " = ? ",
				new String[] { String.valueOf(id) });
	}

	/**
	 * 通过sim卡号来查询人的信息
	 */
	public Person getMeBySim() {
		Person person = null;
		Cursor c = queryAll(TB_PERSON, new String[] { FIELD_PERSON_IMEI },
				new String[] { mHelper.getString(Constants.XMPP_USERNAME) });
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			person = new Person(
					c.getInt(c.getColumnIndexOrThrow(FIELD_ID)),
					c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_ID)),
					c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_NAME)),
					c.getString(c
							.getColumnIndexOrThrow(FIELD_PERSON_DEPARTMENT)),
					c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_POSITION)),
					c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_MOBILE)),
					c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_LATITUDE)),
					c
							.getString(c
									.getColumnIndexOrThrow(FIELD_PERSON_LONGITUDE)),
					c
							.getString(c
									.getColumnIndexOrThrow(FIELD_PERSON_IS_LEADER)));
		}
		if (c != null)
			c.close();
		return person;
	}

	public void insertImportNoticeSent(int type, String[] personIds,
			String content, String sendType, String imageLocalUrl,
			String serverID) {
		openDB();
		ContentValues values;
		values = new ContentValues();
		String date = Tools.getCurrentTimeString();
		String meId = "";
		Cursor c = queryAll(TB_PERSON, new String[] { FIELD_PERSON_IMEI },
				new String[] { mHelper.getString(Constants.XMPP_USERNAME) });
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			meId = c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_ID));
		}
		if (meId == null || "".equals(meId))
			meId = "1";
		values.put(FIELD_IMPORT_NOTICE_SENT_SERVERID, serverID);
		values.put(FIELD_IMPORT_NOTICE_SENT_APPTYPE, type);
		values.put(FIELD_IMPORT_NOTICE_SENT_CONTENT, content);
		values.put(FIELD_IMPORT_NOTICE_SENT_IMAGE_LOCALURL, imageLocalUrl);
		values.put(FIELD_IMPORT_NOTICE_SENT_ISSUE_TIME, date);
		values.put(FIELD_IMPORT_NOTICE_SENT_ISSUER, meId);
		values.put(FIELD_IMPORT_NOTICE_SENT_SEND_TIME, date);
		values.put(FIELD_IMPORT_NOTICE_SENT_SENDER, meId);
		values.put(FIELD_IMPORT_NOTICE_SENT_SEND_TYPE, sendType);
		long id = db.insert(TB_IMPORT_NOTICE_SEND, FIELD_ID, values);
		int len = personIds != null ? personIds.length : 0;
		if (len > 0) {
			for (int i = 0; i < len; i++) {
				values = new ContentValues();
				values.put(FIELD_IMPORT_NOTICE_SENT_CHILD_ID, id);
				values.put(FIELD_IMPORT_NOTICE_SENT_CHILD_APPTYPE, type);
				values.put(FIELD_IMPORT_NOTICE_SENT_RECEIVER, personIds[i]);
				db.insert(TB_IMPORT_NOTICE_SEND_CHILD, FIELD_ID, values);
			}
		} else {
			values = new ContentValues();
			values.put(FIELD_IMPORT_NOTICE_SENT_CHILD_ID, id);
			values.put(FIELD_IMPORT_NOTICE_SENT_CHILD_APPTYPE, type);
			values.put(FIELD_IMPORT_NOTICE_SENT_RECEIVER, "0");
			db.insert(TB_IMPORT_NOTICE_SEND_CHILD, FIELD_ID, values);
		}
		if (c != null)
			c.close();
	}

	public void insertPerson(ArrayList<Person> persons) {
		deleteAll(TB_PERSON);
		if (persons != null) {
			openDB();
			ContentValues values;
			for (Person p : persons) {
				values = new ContentValues();
				values.put(FIELD_PERSON_ID, p.personId);
				values.put(FIELD_PERSON_DEPARTMENT, p.departmentId);
				values.put(FIELD_PERSON_MOBILE, p.mobile);
				values.put(FIELD_PERSON_NAME, p.name);
				values.put(FIELD_PERSON_POSITION, p.positionId);
				values.put(FIELD_PERSON_IS_LEADER, p.isLeader);
				db.insert(TB_PERSON, FIELD_ID, values);
			}
		}
	}

	public void insertDepartment(List<Department> departments) {
		deleteAll(TB_DEPARTMENT);
		if (departments != null) {
			openDB();
			ContentValues values;
			for (Department d : departments) {
				values = new ContentValues();
				values.put(FIELD_DEPARTMENT_ID, d.departmentId);
				values.put(FIELD_DEPARTMENT_NAME, d.name);
				db.insert(TB_DEPARTMENT, FIELD_ID, values);
			}
		}
	}

	public void insertPosition(List<Position> positions) {
		deleteAll(TB_POSITION);
		if (positions != null) {
			openDB();
			ContentValues values;
			for (Position p : positions) {
				values = new ContentValues();
				values.put(FIELD_POSITION_ID, p.positionId);
				values.put(FIELD_POSITION_NAME, p.name);
				db.insert(TB_POSITION, FIELD_ID, values);
			}
		}
	}

	
	public ArrayList<Person> getPersonByDepartmentIds(String[] departmentIds) {
		openDB();
		Cursor c = db.query(TB_PERSON, null, FIELD_PERSON_DEPARTMENT
				+ " in (?)", departmentIds, null, null, null);
		if (c == null || c.getCount() == 0)
			return null;
		c.moveToFirst();
		int len = c.getCount();
		ArrayList<Person> persons = new ArrayList<Person>();
		Person p = null;
		for (int i = 0; i < len; i++) {
			p = new Person();
			p.id = c.getInt(c.getColumnIndexOrThrow(DBHelper.FIELD_ID));
			p.departmentId = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_DEPARTMENT));
			p.latitude = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_LATITUDE));
			p.longitude = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_LONGITUDE));
			p.mobile = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_MOBILE));
			p.name = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_NAME));
			p.personId = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_ID));
			p.positionId = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_POSITION));
			p.isLeader = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_IS_LEADER));
			c.moveToNext();
			persons.add(p);
		}
		if (c != null)
			c.close();
		return persons;
	}

	public ArrayList<Person> getAllPerson() {
		Cursor c = queryAll(TB_PERSON);
		if (c == null || c.getCount() == 0)
			return null;
		c.moveToFirst();
		int len = c.getCount();
		ArrayList<Person> persons = new ArrayList<Person>();
		Person p = null;
		for (int i = 0; i < len; i++) {
			p = new Person();
			p.id = c.getInt(c.getColumnIndexOrThrow(DBHelper.FIELD_ID));
			p.departmentId = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_DEPARTMENT));
			p.latitude = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_LATITUDE));
			p.longitude = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_LONGITUDE));
			p.mobile = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_MOBILE));
			p.name = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_NAME));
			p.personId = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_ID));
			p.positionId = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_POSITION));
			p.isLeader = c.getString(c
					.getColumnIndexOrThrow(DBHelper.FIELD_PERSON_IS_LEADER));
			c.moveToNext();
			persons.add(p);
		}
		if (c != null)
			c.close();
		return persons;
	}

	public ArrayList<Department> getAllDepartment() {
		Cursor c = queryAll(TB_DEPARTMENT);
		if (c == null || c.getCount() == 0)
			return null;
		c.moveToFirst();
		int len = c.getCount();
		ArrayList<Department> departments = new ArrayList<Department>();
		Department d = null;
		for (int i = 0; i < len; i++) {
			d = new Department();
			d.id = c.getInt(c.getColumnIndexOrThrow(FIELD_ID));
			d.departmentId = c.getString(c
					.getColumnIndexOrThrow(FIELD_DEPARTMENT_ID));
			d.name = c
					.getString(c.getColumnIndexOrThrow(FIELD_DEPARTMENT_NAME));
			c.moveToNext();
			departments.add(d);
		}
		if (c != null)
			c.close();
		return departments;
	}

	public void close() {
		if (db != null)
			db.close();
	}

	public void updateImportNotice(int id, String reply, String replyTime) {
		openDB();
		ContentValues values;
		values = new ContentValues();
		Log.e("update is read", String.valueOf(STATUS.IMPORT_NOTICE_READED));
		values.put(FIELD_IMPORT_NOTICE_REPLYCONTENT, reply);
		values.put(FIELD_IMPORT_NOTICE_ISREAD, STATUS.IMPORT_NOTICE_READED);
		values.put(FIELD_IMPORT_NOTICE_REPLYTIME, replyTime);
//		int count = db.update(TB_IMPORT_NOTICE, values, FIELD_IMPORT_NOTICE_ID
//				+ " = ? ", new String[] { String.valueOf(id) });
//		Log.e("----------", "updated [" + count + "] row(s)");
	}

	public void initUploadReply() {
		Cursor c = queryAll(DBHelper.TB_IMPORT_NOTICE,
				new String[] { DBHelper.FIELD_IMPORT_NOTICE_ISREAD },
				new String[] { String.valueOf(STATUS.IMPORT_NOTICE_READED) });
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			do {
				ContentValues values = new ContentValues();
				values
						.put(
								FIELD_UPLOAD_REPLY_SERVICE_ID,
								c
										.getString(c
												.getColumnIndexOrThrow(DBHelper.FIELD_IMPORT_NOTICE_ID)));
				values
						.put(
								FIELD_UPLOAD_REPLY_CONTENT,
								c
										.getString(c
												.getColumnIndexOrThrow(DBHelper.FIELD_IMPORT_NOTICE_REPLYCONTENT)));
				values
						.put(
								FIELD_UPLOAD_REPLY_SEND_TIME,
								c
										.getString(c
												.getColumnIndexOrThrow(DBHelper.FIELD_IMPORT_NOTICE_REPLYTIME)));
				Log
						.i(
								"------------FIELD_UPLOAD_REPLY_SEND_TIME----------",
								c
										.getString(c
												.getColumnIndexOrThrow(DBHelper.FIELD_IMPORT_NOTICE_REPLYTIME)));
				db.insert(TB_UPLOAD_REPLY, FIELD_UPLOAD_REPLY_ID, values);
			} while (c.moveToNext());
		}
	}

	public void insertUploadReply(String serID, String content, String repTime) {
		ContentValues values = new ContentValues();
		values.put(FIELD_UPLOAD_REPLY_SERVICE_ID, serID);
		values.put(FIELD_UPLOAD_REPLY_CONTENT, content);
		values.put(FIELD_UPLOAD_REPLY_SEND_TIME, repTime);
		db.insert(TB_UPLOAD_REPLY, FIELD_UPLOAD_REPLY_ID, values);
		Log.e("----------", "insert [1] row(s)");
		Cursor c = queryAll(TB_UPLOAD_REPLY);
		int count = c.getCount();
		c.close();
		Log.e("----------", "upload_reply count "
				+ (c != null ? count : 0));
	}

	public Cursor getAllUploadReply() {
		return queryAll(DBHelper.TB_UPLOAD_REPLY, null, null);
	}

	public void delUploadReply(String id) {
		deleteAll(TB_UPLOAD_REPLY,
				new String[] { FIELD_UPLOAD_REPLY_SERVICE_ID },
				new String[] { id });
	}

	public String getUploadReply() {
		Cursor c = getAllUploadReply();
		Log.i("---------UPLOAD REPLY COUNT---------", String
				.valueOf((c != null ? c.getCount() : 0)));
		if (c != null && c.getCount() > 0) {
			JSONArray js = new JSONArray();
			c.moveToFirst();
			do {
				String serverID = getVal(c
						.getString(c
								.getColumnIndexOrThrow(DBHelper.FIELD_UPLOAD_REPLY_SERVICE_ID)));
				String content = getVal(c
						.getString(c
								.getColumnIndexOrThrow(DBHelper.FIELD_UPLOAD_REPLY_CONTENT)));
				String sendTime = getVal(c
						.getString(c
								.getColumnIndexOrThrow(DBHelper.FIELD_UPLOAD_REPLY_SEND_TIME)));
				js.put(serverID + DataParse.PREX + content + DataParse.PREX
						+ sendTime);
				Log.i("---------UPLOAD REPLY COUNT---------", serverID
						+ DataParse.PREX + content + DataParse.PREX + sendTime);
			} while (c.moveToNext());
			c.close();	
			return js.length() > 0 ? js.toString() : null;
		}
		if(c!=null){
			c.close();
		}
		return null;
	}

	public String getVal(String str) {
		return str != null ? str : " ";
	}

	public void updateImportNoticeReplys(Reply[] rs) {
		openDB();
		ContentValues values;
		for (int i = 0; i < rs.length; i++) {
			values = new ContentValues();
			values.put(FIELD_IMPORT_NOTICE_SENT_REPLYCONTENT, rs[i].content);
			values.put(FIELD_IMPORT_NOTICE_SENT_REPLYTIME, rs[i].rTime);
			values.put(FIELD_IMPORT_NOTICE_SENT_ISREAD,
					STATUS.IMPORT_NOTICE_READED);
			int count = db.update(TB_IMPORT_NOTICE_SEND_CHILD, values,
					FIELD_IMPORT_NOTICE_SENT_CHILD_ID + " = ? and "
							+ FIELD_IMPORT_NOTICE_SENT_RECEIVER + " = ?",
					new String[] { String.valueOf(rs[i].nID), rs[i].oID });
			Log.e("----------", "updated [" + count + "] row(s)");
		}
	}


	// public void delete()
	// {
	// openDB();
	// db.delete(table, whereClause, whereArgs)；
	// }

	public void deleteAll(String table) {
		openDB();
		db.delete(table, null, null);
	}

	/**
	 * 删除指定的记录
	 * 
	 * @param table
	 * @return
	 */
	public void deleteAll(String table, String[] fields, String[] values) {
		openDB();
		if (fields == null || fields.length == 0) {
			db.delete(table, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_AND;
				where += " " + fields[i] + " = ? ";
			}
			db.delete(table, where, values);
		}
	}

	/**
	 * 删除指定的记录
	 * 
	 * @param table
	 * @return
	 */
	public void deleteLikeAll(String table, String[] fields, String[] values) {
		openDB();
		if (fields == null || fields.length == 0) {
			db.delete(table, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_AND;
				where += " " + fields[i] + " like ? ";
			}
			db.delete(table, where, values);
		}
	}

	public Cursor queryAll(String table) {
		openDB();
		Cursor c = db.query(table, null, null, null, null, null, null);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public String getPersonName(String id) {
		Cursor c = queryAll(TB_PERSON, new String[] { FIELD_PERSON_ID },
				new String[] { id });
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			return c.getString(c.getColumnIndexOrThrow(FIELD_PERSON_NAME));
		}
		return "";
	}

	public Cursor queryAll(String table1, String table2, String field1,
			String field2, String[] fields, String[] values, String order) {
		openDB();
		String where = "";
		for (int i = 0; i < fields.length; i++) {
			if (i > 0)
				where += QUERY_WHERE_AND;
			where += " " + fields[i] + " = ? ";
		}
		where += " " + QUERY_WHERE_AND + " tb1" + "." + field1 + " = tb2" + "."
				+ field2;
		Cursor c = db.query(table1 + " as tb1," + table2 + " as tb2 ", null,
				where, values, null, null, order);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryAll(String table1, String table2, String field1,
			String field2, String[] fields, String[] values, String order,
			String limit) {
		openDB();
		String where = "";
		for (int i = 0; i < fields.length; i++) {
			if (i > 0)
				where += QUERY_WHERE_AND;
			where += " " + fields[i] + " = ? ";
		}
		where += " " + QUERY_WHERE_AND + " tb1" + "." + field1 + " = tb2" + "."
				+ field2;
		Cursor c = db.query(table1 + " as tb1," + table2 + " as tb2 ", null,
				where, values, null, null, order, limit);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryAll(String table1, String table2, String field1,
			String field2, String[] fields, String[] values) {
		openDB();
		String where = "";
		for (int i = 0; i < fields.length; i++) {
			if (i > 0)
				where += QUERY_WHERE_AND;
			where += " " + fields[i] + " = ? ";
		}
		where += " " + QUERY_WHERE_AND + " tb1." + field1 + " = tb2." + field2;
		Cursor c = db.query(table1 + " as tb1," + table2 + " as tb2 ", null,
				where, values, null, null, null);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryAll(String table, String[] fields) {
		openDB();
		Cursor c = db.query(table, fields, null, null, null, null, null);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryOrAll(String table, String[] fields, String[] values) {
		openDB();
		Cursor c = null;
		if (fields == null || fields.length == 0) {
			c = db.query(table, null, null, null, null, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_OR;
				where += " " + fields[i] + " = ? ";
			}
			Log.d("------------", where);
			c = db.query(table, null, where, values, null, null, null);
		}
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryNotAll(String table, String[] fields, String[] values) {
		openDB();
		Cursor c = null;
		if (fields == null || fields.length == 0) {
			c = db.query(table, null, null, null, null, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_OR;
				where += " " + fields[i] + " != ? ";
			}
			Log.d("------------", where);
			c = db.query(table, null, where, values, null, null, null);
		}
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryAll(String table, String[] fields, String[] values) {
		openDB();
		Cursor c = null;
		if (fields == null || fields.length == 0) {
			c = db.query(table, null, null, null, null, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_AND;
				where += " " + fields[i] + " = ? ";
			}
			Log.d("------------", where);
			c = db.query(table, null, where, values, null, null, null);
		}
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryAll(String table, String[] fields, String[] values,
			String order) {
		openDB();
		Cursor c = null;
		if (fields == null || fields.length == 0) {
			c = db.query(table, null, null, null, null, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_AND;
				where += " " + fields[i] + " = ? ";
			}
			c = db.query(table, null, where, values, null, null, order);
		}
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryAll(String table, String[] columns, String[] fields,
			String[] values) {
		openDB();
		Cursor c = null;
		if (fields == null || fields.length == 0) {
			c = db.query(table, null, null, null, null, null, null);
		} else {
			String where = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_AND;
				where += " " + fields[i] + " = ? ";
			}
			c = db.query(table, columns, where, values, null, null, null);
		}
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryInAll(String table, String fields, String[] values,
			String order) {
		openDB();
		Cursor c = null;
		String value = "";
		for (int i = 0; i < values.length; i++) {
			if (i != 0)
				value += ",";
			value += "'" + values[i] + "'";
		}
		c = db.query(table, null, fields + " in(" + value + ") ", null, null,
				null, null);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryInAll(String table1, String table2, String field1,
			String field2, String[] wheres, String[] args, String field,
			String[] values, String order) {
		openDB();
		String value = "";
		String where = "";
		for (int i = 0; i < values.length; i++) {
			if (i != 0)
				value += ",";
			value += "'" + values[i] + "'";
		}
		for (int i = 0; i < wheres.length; i++) {
			if (i > 0)
				where += QUERY_WHERE_AND;
			where += " " + wheres[i] + " = ? ";
		}
		where += " " + QUERY_WHERE_AND + " tb1." + field1 + " = tb2." + field2
				+ " " + QUERY_WHERE_AND + " " + field + " in(" + value + ")";
		Cursor c = db.query(table1 + " as tb1," + table2 + " as tb2 ", null,
				where, args, null, null, order);
		if (c != null)
			c.moveToFirst();
		return c;
	}

	public Cursor queryLikeAll(String table, String[] fields, String[] values) {
		openDB();
		Cursor c = null;
		if (fields == null || fields.length == 0) {
			c = db.query(table, null, null, null, null, null, null);
		} else {
			String where = "";
			String vals = "";
			for (int i = 0; i < fields.length; i++) {
				if (i > 0)
					where += QUERY_WHERE_AND;
				where += " " + fields[i] + " like ? ";
				vals += values[i];
			}
			c = db.query(table, null, where, values, null, null, null);
			Log.e("-----sql like --------", where + vals);
		}
		if (c != null)
			c.moveToFirst();
		Log.e("-----sql like res count--------", String.valueOf((c != null ? c
				.getCount() : 0)));
		return c;
	}

	public Cursor nearlyNotice() {
		String where = " " + DBHelper.FIELD_IMPORT_NOTICE_ISREAD + " = ? and ("
				+ DBHelper.FIELD_IMPORT_NOTICE_SEND_TIME + " like ? or "
				+ DBHelper.FIELD_IMPORT_NOTICE_SEND_TIME + " like ?)";
		Cursor c = queryLikeAll(DBHelper.TB_IMPORT_NOTICE, where, new String[] {
				String.valueOf(STATUS.IMPORT_NOTICE_UN_READ),
				Tools.getAlertDateString(0) + "%",
				Tools.getAlertDateString(-1) + "%" }, " order by "
				+ DBHelper.FIELD_IMPORT_NOTICE_ID + " desc");
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			do {
				String rTime = c
						.getString(c
								.getColumnIndex(DBHelper.FIELD_IMPORT_NOTICE_SEND_TIME));
				if (Tools.isNear(rTime)) {
					return queryAll(DBHelper.TB_IMPORT_NOTICE,
							new String[] { FIELD_ID }, new String[] { c
									.getString(c.getColumnIndex(FIELD_ID)) });
				}
			} while (c.moveToNext());
		}
		return null;
	}

	public Cursor queryLikeAll(String table, String where, String[] values,
			String order) {
		openDB();
		Cursor c = null;
		String vals = "";
		for (int i = 0; i < values.length; i++) {
			vals += " : " + values[i];
		}
		c = db.query(table, null, where, values, null, null, null);
		Log.e("-----sql like --------", where + vals);
		if (c != null)
			c.moveToFirst();
		Log.e("-----sql like res --------", String.valueOf(c != null ? c
				.getCount() : 0));
		// test();
		return c;
	}

	public void test() {
		Cursor c = queryAll(TB_IMPORT_NOTICE);
		if (c != null && c.getCount() > 0) {
			do {
				Log
						.e(
								"---------------TB_IMPORT_NOTICE----------------",
								String
										.valueOf(c
												.getString(c
														.getColumnIndex(FIELD_IMPORT_NOTICE_ISREAD)))
										+ " : "
										+ String
												.valueOf(c
														.getString(c
																.getColumnIndex(FIELD_IMPORT_NOTICE_SEND_TIME))));
			} while (c.moveToNext());
		}
	}

	public String getIsReadString(int type) {
		switch (type) {
		case STATUS.IMPORT_NOTICE_UN_READ:
			return "未读";
		case STATUS.IMPORT_NOTICE_READED:
			return "已读";
		}
		return "";
	}

	public String getMyName(String mobileID) {
		String name = "";
		Cursor c = queryAll(TB_PERSON, new String[] { FIELD_PERSON_IMEI },
				new String[] { mobileID });
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			name = c.getString(c.getColumnIndex(FIELD_PERSON_NAME));
			Log.e("----------name:", name);
			c.close();
		}
		return name;
	}

	public static interface STATUS {
		/**
		 * 重要通告未读状态
		 */
		public final static int IMPORT_NOTICE_UN_READ = 0;
		/**
		 * 重要通告已读状态
		 */
		public final static int IMPORT_NOTICE_READED = 1;

		public final static String LEADER_HIGHT = "0"; // 高层领导
		public final static String LEADER_MIDDLE = "1"; // 中层领导
		public final static String LEADER_SMALL = "2"; // 普通人员

		public final static String MATCH_IS_GROUP_YES = "YES";
		public final static String MATCH_IS_GROUP_NO = "NO";
	}

	public static final String QUERY_WHERE_ARG = " like ? ";
	public static final String QUERY_WHERE_OR = " or ";
	public static final String QUERY_WHERE_AND = " and ";
	public static final String QUERY_SPACE = " ? ";
	public static final String TB_DELETE = "DROP TABLE IF EXISTS ";

	private final static String DATABASE_NAME = "eipc.db";
	private final static int DATABASE_VERSION = 2;
	/* 表名 */
	public final static String TB_IMPORT_NOTICE = "import_notice";// 重要通告
	public final static String TB_PERSON = "person"; // 人员表
	public final static String TB_PERSON2 = "person2"; // 人员表（领导使用）
	public final static String TB_DEPARTMENT = "department"; // 部门表
	public final static String TB_POSITION = "position"; // 职责

	// add
	public final static String TB_IMPORT_NOTICE_SEND = "import_notice_send";// 重要通告(已发送)
	public final static String TB_IMPORT_NOTICE_SEND_CHILD = "import_notice_send_child";// 重要通告(已发送)

	public final static String TB_UPLOAD_REPLY = "upload_reply"; // 回复同步表


	public void delete1006() {
		try {
			openDB();
			db.delete(TB_IMPORT_NOTICE, null, null);
			db.delete(TB_IMPORT_NOTICE_SEND, null, null);
		} catch (Exception e) {

		}

	}

	/* 字段 */
	public final static String FIELD_ID = "_id";

	/* 重要通告 (接收到的,别人发送过来的) */
	// 这个ID存服务器发送过来的ID，所以不是流水号，可能不连续
	public final static String FIELD_IMPORT_NOTICE_ID = "id";
	// 功能类型
	public final static String FIELD_IMPORT_NOTICE_APPTYPE = "appType";
	public final static String FIELD_IMPORT_NOTICE_CONTENT = "content";
	public final static String FIELD_IMPORT_NOTICE_IMAGE_WEBURL = "image_weburl";
	public final static String FIELD_IMPORT_NOTICE_IMAGE_LOCALURL = "image_localurl";
	public final static String FIELD_IMPORT_NOTICE_ISSUE_TIME = "issue_time";
	public final static String FIELD_IMPORT_NOTICE_ISSUER = "issuer";
	public final static String FIELD_IMPORT_NOTICE_SEND_TIME = "send_time";
	public final static String FIELD_IMPORT_NOTICE_SENDER = "sender";
	public final static String FIELD_IMPORT_NOTICE_SEND_TYPE = "send_type";
	public final static String FIELD_IMPORT_NOTICE_TOP = "top";
	// 本终端的回复情况
	public final static String FIELD_IMPORT_NOTICE_ISREAD = "isread";
	public final static String FIELD_IMPORT_NOTICE_REPLYCONTENT = "replyContent";
	public final static String FIELD_IMPORT_NOTICE_REPLYTIME = "replyTime";

	// 重要通知获取方式 0:push; 1:轮询; 2:其它
	// public final static String FIELD_IMPORT_NOTICE_TYPE = "noticeType";

	/* 重要通告(已发送，我发送给别人的) */
	// 本地发送的重要通知的序列号，流水号
	// public final static String FIELD_IMPORT_NOTICE_SENT_ID= "id";
	// 功能类型
	public final static String FIELD_IMPORT_NOTICE_SENT_APPTYPE = "appType";
	// 该通知在服务器端的ID号，若为空则代表未发送，否则为已发送
	public final static String FIELD_IMPORT_NOTICE_SENT_SERVERID = "serverid";
	public final static String FIELD_IMPORT_NOTICE_SENT_CONTENT = "content";
	public final static String FIELD_IMPORT_NOTICE_SENT_IMAGE_LOCALURL = "image_localurl";
	// 这里签发和发送的字段都加上，实际填充的时候都是该终端的使用者
	public final static String FIELD_IMPORT_NOTICE_SENT_ISSUE_TIME = "issue_time";
	public final static String FIELD_IMPORT_NOTICE_SENT_ISSUER = "issuer";
	public final static String FIELD_IMPORT_NOTICE_SENT_SEND_TIME = "send_time";
	public final static String FIELD_IMPORT_NOTICE_SENT_SENDER = "sender";
	public final static String FIELD_IMPORT_NOTICE_SENT_SEND_TYPE = "send_type";

	/*
	 * 重要通告(已发送子表，记录每条信息的接受者和回复状态) ；注意：本张表暂时只使用前面两个字段 查看接受者状态的实现，都去读服务器URL
	 */
	// 本地发送的重要通知的序列号，流水号；用于与主表关联
	public final static String FIELD_IMPORT_NOTICE_SENT_CHILD_ID = "child_id";
	// 功能类型
	public final static String FIELD_IMPORT_NOTICE_SENT_CHILD_APPTYPE = "appType";
	// 接受者的电话号码
	public final static String FIELD_IMPORT_NOTICE_SENT_RECEIVER = "receiver";
	public final static String FIELD_IMPORT_NOTICE_SENT_ISREAD = "isread";
	public final static String FIELD_IMPORT_NOTICE_SENT_REPLYCONTENT = "ReplyContent";
	public final static String FIELD_IMPORT_NOTICE_SENT_REPLYTIME = "ReplyTime";

	/* 人员表 */
	public final static String FIELD_PERSON_ID = "id";
	public final static String FIELD_PERSON_NAME = "name";
	public final static String FIELD_PERSON_DEPARTMENT = "departmentId";
	public final static String FIELD_PERSON_POSITION = "positionId";
	public final static String FIELD_PERSON_MOBILE = "mobile";
	public final static String FIELD_PERSON_IMEI = "imei";
	public final static String FIELD_PERSON_SHORT_MOBILE = "short_mobile";
	public final static String FIELD_PERSON_IS_LEADER = "is_leader";

	// 查看人员位置时的GPS坐标
	public final static String FIELD_PERSON_LATITUDE = "geoLatitude";
	public final static String FIELD_PERSON_LONGITUDE = "geoLongitude";

	/* 部门表 */
	public final static String FIELD_DEPARTMENT_ID = "id";
	public final static String FIELD_DEPARTMENT_NAME = "name";
	/* 职责表 */
	public final static String FIELD_POSITION_ID = "id";
	public final static String FIELD_POSITION_NAME = "name";


	/* 回复同步表 */
	public final static String FIELD_UPLOAD_REPLY_ID = "upload_reply_id";
	public final static String FIELD_UPLOAD_REPLY_SERVICE_ID = "service_command_id";
	public final static String FIELD_UPLOAD_REPLY_CONTENT = "content";
	public final static String FIELD_UPLOAD_REPLY_SEND_TIME = "send_time";

	// 重要通知
	private final static String SQL_IMPORT_NOTICE = "CREATE TABLE "
			+ TB_IMPORT_NOTICE + " (" + FIELD_ID
			+ " integer primary key autoincrement, " + " "
			+ FIELD_IMPORT_NOTICE_ID + " integer," + " "
			+ FIELD_IMPORT_NOTICE_APPTYPE + " integer," + " "
			+ FIELD_IMPORT_NOTICE_CONTENT + " text," + " "
			+ FIELD_IMPORT_NOTICE_IMAGE_WEBURL + " varchar(100) ," + " "
			+ FIELD_IMPORT_NOTICE_IMAGE_LOCALURL + " varchar(100) ," + " "
			+ FIELD_IMPORT_NOTICE_ISSUE_TIME + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_ISSUER + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_SEND_TIME + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_SENDER + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_SEND_TYPE + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_ISREAD + " integer," + " "
			+ FIELD_IMPORT_NOTICE_REPLYCONTENT + " text," + " "
			+ FIELD_IMPORT_NOTICE_TOP + " varchar(5)," + " "
			+ FIELD_IMPORT_NOTICE_REPLYTIME + " varchar(30));";

	/* 重要通告(已发送，我发送给别人的) */
	private final static String SQL_IMPORT_NOTICE_SEND = "CREATE TABLE "
			+ TB_IMPORT_NOTICE_SEND + " (" + FIELD_ID
			+ " integer primary key autoincrement, " + " "
			+ FIELD_IMPORT_NOTICE_SENT_APPTYPE + " integer," + " "
			+ FIELD_IMPORT_NOTICE_SENT_SERVERID + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_CONTENT + " text," + " "
			+ FIELD_IMPORT_NOTICE_SENT_IMAGE_LOCALURL + " varchar(100) ," + " "
			+ FIELD_IMPORT_NOTICE_SENT_SEND_TIME + " varchar(30) ," + " "
			+ FIELD_IMPORT_NOTICE_SENT_ISSUER + " varchar(30) ," + " "
			+ FIELD_IMPORT_NOTICE_SENT_SENDER + " varchar(30) ," + " "
			+ FIELD_IMPORT_NOTICE_SENT_SEND_TYPE + " varchar(30) ," + " "
			+ FIELD_IMPORT_NOTICE_SENT_ISSUE_TIME + " varchar(30));";

	/*
	 * 重要通告(已发送子表，记录每条信息的接受者和回复状态) ；注意：本张表暂时只使用前面两个字段 查看接受者状态的实现，都去读服务器URL
	 */
	private final static String SQL_IMPORT_NOTICE_SEND_CHILD = "CREATE TABLE "
			+ TB_IMPORT_NOTICE_SEND_CHILD + " (" + " " + FIELD_ID
			+ " integer primary key autoincrement, " + " "
			+ FIELD_IMPORT_NOTICE_SENT_CHILD_ID + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_SENT_CHILD_APPTYPE + " varchar(30)," + " "
			+ FIELD_IMPORT_NOTICE_SENT_RECEIVER + " integer," + " "
			+ FIELD_IMPORT_NOTICE_SENT_REPLYCONTENT + " text," + " "
			+ FIELD_IMPORT_NOTICE_SENT_ISREAD + " integer ," + " "
			+ FIELD_IMPORT_NOTICE_SENT_REPLYTIME + " varchar(30));";

	private final static String SQL_PERSON = "CREATE TABLE " + TB_PERSON + " ("
			+ FIELD_ID + " integer primary key autoincrement, " + " "
			+ FIELD_PERSON_ID + " integer," + " " + FIELD_PERSON_NAME
			+ " text," + " " + FIELD_PERSON_DEPARTMENT + " integer," + " "
			+ FIELD_PERSON_MOBILE + " varchar(30) ," + " " + FIELD_PERSON_IMEI
			+ " varchar(30)," + " " + FIELD_PERSON_SHORT_MOBILE
			+ " varchar(30)," + " " + FIELD_PERSON_LATITUDE + " varchar(30),"
			+ " " + FIELD_PERSON_LONGITUDE + " varchar(30) ," + " "
			+ FIELD_PERSON_IS_LEADER + " integer ," + " "
			+ FIELD_PERSON_POSITION + " varchar(30));";


	private final static String SQL_DEPARTMENT = "CREATE TABLE "
			+ TB_DEPARTMENT + " (" + FIELD_ID
			+ " integer primary key autoincrement, " + " "
			+ FIELD_DEPARTMENT_ID + " integer, " + " " + FIELD_DEPARTMENT_NAME
			+ " varchar(30));";

	private final static String SQL_POSITION = "CREATE TABLE " + TB_POSITION
			+ " (" + FIELD_ID + " integer primary key autoincrement, " + " "
			+ FIELD_POSITION_ID + " integer," + " " + FIELD_POSITION_NAME
			+ " varchar(30));";


//	private final static String SQL_UPLOAD_REPLY = "CREATE TABLE IF NOT EXISTS "
//			+ TB_UPLOAD_REPLY
//			+ " ("
//			+ FIELD_UPLOAD_REPLY_ID
//			+ " integer primary key autoincrement, "
//			+ " "
//			+ FIELD_UPLOAD_REPLY_SERVICE_ID
//			+ " varchar(30), "
//			+ " "
//			+ FIELD_UPLOAD_REPLY_CONTENT
//			+ " varchar(500), "
//			+ " "
//			+ FIELD_UPLOAD_REPLY_SEND_TIME + " varchar(30));";


//	class ContactsTask extends AsyncTask<Void, Void, Void> {
//		@Override
//		protected Void doInBackground(Void... params) {
//			try {
//				Tools.buildContacts(context, queryAll(DBHelper.TB_PERSON));
//			} catch (Exception e) {
//				Log.e("--------ContactsTask", "Error");
//			}
//			return null;
//		}
//	}

	// insert by czd 20110405
	public final static String TB_VERSION = "version";
	public final static String FIELD_VERSION_KEY = "key";
	public final static String FIELD_VERSION_VALUE = "value";
	private final static String SQL_TB_VERSION = "CREATE TABLE " + TB_VERSION
			+ " (" + FIELD_ID + " integer primary key autoincrement, " + " "
			+ FIELD_VERSION_KEY + " text," + " " + FIELD_VERSION_VALUE
			+ " integer);";

	public void insertStaff(Staff[] persons) {
		deleteAll(TB_PERSON);

		if (persons != null) {
			openDB();
			ContentValues values;
			for (Staff p : persons) {
				if (p == null) {
					continue;
				}
				values = new ContentValues();
				values.put(FIELD_PERSON_ID, p.personId);
				values.put(FIELD_PERSON_DEPARTMENT, p.departmentId);
				values.put(FIELD_PERSON_IMEI, p.imei);
				values.put(FIELD_PERSON_MOBILE, p.mobile);
				values.put(FIELD_PERSON_NAME, p.name);
				values.put(FIELD_PERSON_POSITION, p.positionId);
				values.put(FIELD_PERSON_IS_LEADER, p.isLeader);
				db.insert(TB_PERSON, FIELD_ID, values);
			}
		}

	}

	public Map<String, Integer> queryVersion() {
		openDB();
		Map<String, Integer> map = new HashMap<String, Integer>();
		Cursor cursor = db
				.query(TB_VERSION, null, null, null, null, null, null);
		if (cursor != null) {
			try {
				if (cursor.moveToFirst()) {

					int keyIndex = cursor.getColumnIndex(FIELD_VERSION_KEY);
					int valueIndex = cursor.getColumnIndex(FIELD_VERSION_VALUE);

					do {

						map.put(cursor.getString(keyIndex), cursor
								.getInt(valueIndex));

					} while (cursor.moveToNext());

				}
			} finally {
				cursor.close();
			}
		}
		return map;
	}

	public void insertVersion(Map<String, Integer> map) {
		openDB();

		deleteAll(TB_VERSION);

		ContentValues values;
		for (Map.Entry<String, Integer> entry : map.entrySet()) {

			try {

				values = new ContentValues();
				values.put(FIELD_VERSION_KEY, entry.getKey());
				values.put(FIELD_VERSION_VALUE, entry.getValue());

				db.insert(TB_VERSION, FIELD_ID, values);

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}
